"Tilting" a video game cartridge is the process of interrupting the flow of data from the game cartridge to the console. The result of this can be anything, from deforming the character to mess up the game's music to access the game's debug menu. Tilting the cartridge on Super Mario 64, for example, can affect the video game to make all sound effects/music corrupted and make Mario sink into the ground and lose his legs.
Most of the time, especially on Super Nintendo, cartridge tilting can result in crashes (scrambled graphics and music usually occur), but rarely destroy the cartridge ROM.